package com.informet.ipd.modelo.dao;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;


import com.informet.ipd.modelo.vo.UbigeoVO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class AlumnoDao extends DBase{


  Connection conn = null;

    public AlumnoDao() {
        super();
    }




    public void  setListAlumno(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{
          
          bean.setCodAlumno(rs.getString("NUM_CODALUMCCD_PK"));
          bean.setApePaterno(rs.getString("TX_APATERNOACCD"));
          bean.setApeMaterno(rs.getString("TX_AMATERNOACCD"));
          bean.setNombre(rs.getString("TX_NOMBREACCD"));
          bean.setFecNacimiento(rs.getString("fecNacimiento"));
          bean.setCondicion(rs.getString("NUM_CONDICIONACCD"));
          bean.setPeso(rs.getString("NUM_PESOACCD"));
          bean.setTalla(rs.getString("NUM_TALLAACCD"));
          bean.setCodCcd(Integer.parseInt(rs.getString("NUM_CCD_PK")));

    }


public List getListAlumno(CrecerConDeporteVO bean) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        //lista=null;
        String sql = "";
        
        try {
            /* PARTE DE CODIGO SIRVE PARA RECUPERAR REGISTROS DE UN PROCEDURE 
            ArrayList lista1 = new ArrayList(0);
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call RetornaConjuntoDeRegistros(?)}");
            cs.setString(1, "");
            rs = cs.executeQuery();
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListAlumno(bean1, rs);
                lista.add(bean1);
            }
            */
            
           
            

            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();
                        sql = "select "+
                        " isnull(convert(varchar,DT_NACIMIENTOACCD,103),'') as fecNacimiento, "+
                        " * "+
                        " from me_alumnosccd "+
                        " where num_ccd_pk="+bean.getCodCcd();

                  if((bean.getApePaterno()!=null ))
                      if((!bean.getApePaterno().equals("") ))
                      {
                        sql=sql+"     and TX_APATERNOACCD like '%"+bean.getApePaterno()+"%'";
                      }
                  if((bean.getApeMaterno()!=null ))
                      if((!bean.getApeMaterno().equals("") ))
                        sql=sql+"     and TX_AMATERNOACCD like '%"+bean.getApeMaterno()+"%'";

                  if((bean.getNombre()!=null ))
                      if((!bean.getNombre().equals("") ))
                        sql=sql+"     and TX_NOMBREACCD like '%"+bean.getNombre()+"%'";

                  if((bean.getCondicion()!=null ))
                      if((!bean.getCondicion().equals("-1") ))
                        sql=sql+"     and NUM_CONDICIONACCD ="+bean.getCondicion();

                  if((bean.getPeso()!=null))
                      if((!bean.getPeso().equals("") ))
                            sql=sql+" and NUM_PESOACCD="+bean.getPeso();

                 if((bean.getTalla()!=null ))
                     if((!bean.getTalla().equals("") ))
                            sql=sql+" and NUM_TALLAACCD="+bean.getTalla();

                 

            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListAlumno(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
       

      public String spMan(CrecerConDeporteVO bean) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {
        	
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_MeAlumnoCcd(?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            cs.setString(1, bean.getCodAlumno());
            cs.setString(2, bean.getApePaterno());
            cs.setString(3, bean.getApeMaterno());
            cs.setString(4, bean.getNombre());
            cs.setString(5, bean.getFecNacimiento());
            cs.setString(6, bean.getCondicion());
            cs.setString(7, bean.getPeso());
            cs.setString(8, bean.getTalla());
            cs.setInt(9, bean.getCodCcd());
            cs.setString(10, bean.getTipoOper());
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
